﻿-- Add your test scenario here --
--for attribute validation and dropdowntype attributes

alter table X_NODE_TYPE_ATTR
add IS_REQUIRED bit

alter table X_NODE_TYPE_ATTR
add DROPDOWN_VAL varchar(500)

alter table X_NODE_TYPE_ATTR
add CONTROL_TYPE varchar(50)


CREATE FUNCTION [dbo].[GET_IsLeaf]
(
@Node_Id AS INT
)
RETURNS VARCHAR(1)
AS
BEGIN
	DECLARE @IsLeaf AS VARCHAR(1)
	DECLARE @CNT int
	
	 SELECT @CNT= COUNT(ND.NODE_ID) 
	 FROM C_PRD_NODES ND  
	 WHERE PARENT_ID=@Node_Id AND ND.HIERARCHY_ID = ND.HIERARCHY_ID 
	 AND CONVERT(VARCHAR,ND.END_DATE,101) >=  '08/30/2011' 
	 AND  CONVERT(VARCHAR,ND.START_DATE,101) <= '08/30/2011'
	 
	 if @CNT > 0 
	 Begin
		SET @IsLeaf = '0'
	 END
	 else 
	  SET @IsLeaf = '1'
	  
	 RETURN @IsLeaf
END


-- 06/09/2011 alter function

ALTER FUNCTION [dbo].[GET_IsLeaf]
(
@Node_Id AS INT,
@DATE VARCHAR(12)
)
RETURNS VARCHAR(1)
AS
BEGIN
	 DECLARE @IsLeaf AS VARCHAR(1)
	 DECLARE @CNT int
	
	 SELECT @CNT= COUNT(ND.NODE_ID) 
	 FROM C_PRD_NODES ND  
	 WHERE PARENT_ID=@Node_Id AND ND.HIERARCHY_ID = ND.HIERARCHY_ID 
	 AND CONVERT(VARCHAR,ND.END_DATE,101) >=  @DATE 
	 AND  CONVERT(VARCHAR,ND.START_DATE,101) <= @DATE 
	 
	 if @CNT > 0 
	 Begin
		SET @IsLeaf = '0'
	 END
	 else 
	    SET @IsLeaf = '1'
	  
	 RETURN @IsLeaf
END
GO

--9 sep 2011
create FUNCTION [dbo].[GET_validParent]
(
@NodeTypeId INT,
@HierarchyId INT
)

RETURNS VARCHAR(MAX)
AS
BEGIN
	
	DECLARE @listStr VARCHAR(MAX)
	SELECT @listStr = COALESCE(@listStr+',' ,'') +Convert(VARCHAR,PARENT_NODE_TYPE_ID)
	FROM X_HIER_NODE_TYPE_PARENT 
	WHERE NODE_TYPE_ID=@NodeTypeId AND HIERARCHY_ID =@HierarchyId
	--WHERE NODE_TYPE_ID=1 AND HIERARCHY_ID =1
	--print @listStr 

	 RETURN @listStr
END




select * from X_NODE_TYPE_ATTR 

update X_NODE_TYPE_ATTR set DROPDOWN_VAL ='a,b,ctest'
where ATTR_COLUMN ='CAT_DESC'

update X_NODE_TYPE_ATTR set IS_REQUIRED =1

update X_NODE_TYPE_ATTR set CONTROL_TYPE ='textbox'

update X_NODE_TYPE_ATTR set CONTROL_TYPE ='calender'
where ATTR_COLUMN ='CAT_CODE'

update X_NODE_TYPE_ATTR set CONTROL_TYPE ='dropdown'
where ATTR_COLUMN ='CAT_DESC'

update X_NODE_TYPE_ATTR set ATTR_DATA_TYPE ='NUMBER'
where ATTR_COLUMN ='CAT_NM'


--16 september 2011

DROP TABLE C_PRD_CUSTOMER_REL
DROP TABLE X_MASTER_LIST_COLUMNS
DROP TABLE X_MASTER_LIST
DROP TABLE X_HIER_MASTER_LIST_REL
DROP TABLE M_PRODUCTS


CREATE TABLE [dbo].[C_PRD_PRODUCT_REL](
	[NODE_ID] [float] NULL,
	[PRODUCT_ID] [float] NULL,
	[START_DATE] [datetime] NULL,
	[END_DATE] [datetime] NULL,
) ON [PRIMARY]
GO
INSERT [dbo].[C_PRD_PRODUCT_REL] ([NODE_ID], [PRODUCT_ID], [START_DATE], [END_DATE]) VALUES (1, 1, CAST(0x00009FCA00000000 AS DateTime), CAST(0x00009FCA00000000 AS DateTime))
INSERT [dbo].[C_PRD_PRODUCT_REL] ([NODE_ID], [PRODUCT_ID], [START_DATE], [END_DATE]) VALUES (2, 2, CAST(0x00009FCA00000000 AS DateTime), CAST(0x00009FCA00000000 AS DateTime))
/****** Object:  Table [dbo].[X_NODE_TYPE_ATTR]    Script Date: 09/16/2011 14:36:57 ******/


GO
CREATE TABLE [dbo].[X_MASTER_LIST_COLUMNS](
	[LIST_COL_ID] [float] NOT NULL,
	[LIST_ID] [float] NULL,
	[DISPLAY] [varchar](255) NULL,
	[FIELD_NAME] [varchar](255) NULL,
	[DATA_TYPE] [varchar](255) NULL,
	[PRECISION] [varchar](255) NULL,
	[IS_REQUIRED] [bit] NULL,
	[DROPDOWN_VAL] [varchar](500) NULL,
	[CONTROL_TYPE] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[X_MASTER_LIST_COLUMNS] ([LIST_COL_ID], [LIST_ID], [DISPLAY], [FIELD_NAME], [DATA_TYPE], [PRECISION], [IS_REQUIRED], [DROPDOWN_VAL], [CONTROL_TYPE]) VALUES (1, 2, N'Id', N'PRODUCT_ID', N'NUMBER', NULL, 1, NULL, N'TEXTBOX')
INSERT [dbo].[X_MASTER_LIST_COLUMNS] ([LIST_COL_ID], [LIST_ID], [DISPLAY], [FIELD_NAME], [DATA_TYPE], [PRECISION], [IS_REQUIRED], [DROPDOWN_VAL], [CONTROL_TYPE]) VALUES (2, 2, N'Product Name', N'PRODUCT_NAME', N'VARCHAR', N'100', 0, NULL, N'TEXTBOX')
INSERT [dbo].[X_MASTER_LIST_COLUMNS] ([LIST_COL_ID], [LIST_ID], [DISPLAY], [FIELD_NAME], [DATA_TYPE], [PRECISION], [IS_REQUIRED], [DROPDOWN_VAL], [CONTROL_TYPE]) VALUES (3, 2, N'Brand', N'PRODUCT_BRAND', N'VARCHAR', N'100', 0, NULL, N'TEXTBOX')
INSERT [dbo].[X_MASTER_LIST_COLUMNS] ([LIST_COL_ID], [LIST_ID], [DISPLAY], [FIELD_NAME], [DATA_TYPE], [PRECISION], [IS_REQUIRED], [DROPDOWN_VAL], [CONTROL_TYPE]) VALUES (4, 2, N'Size', N'PRODUCT_SIZE', N'VARCHAR', N'200', 0, NULL, N'TEXTBOX')
/****** Object:  Table [dbo].[X_MASTER_LIST]    Script Date: 09/16/2011 14:36:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[X_MASTER_LIST](
	[LIST_ID] [float] NULL,
	[LIST_NAME] [varchar](255) NULL,
	[TABLE_NAME] [varchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[X_MASTER_LIST] ([LIST_ID], [LIST_NAME], [TABLE_NAME]) VALUES (1, N'Customer', N'M_CUSTOMER')
INSERT [dbo].[X_MASTER_LIST] ([LIST_ID], [LIST_NAME], [TABLE_NAME]) VALUES (2, N'Product', N'M_PRODUCTS')
/****** Object:  Table [dbo].[X_HIERARCHIES]    Script Date: 09/16/2011 14:36:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[X_HIER_MASTER_LIST_REL](
	[HIERARCHY_ID] [float] NULL,
	[MASTER_LIST_ID] [float] NULL,
	[TABLE_NAME] [varchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[X_HIER_MASTER_LIST_REL] ([HIERARCHY_ID], [MASTER_LIST_ID], [TABLE_NAME]) VALUES (2, 1, N'C_SFA_CUSTOMER_REL')
INSERT [dbo].[X_HIER_MASTER_LIST_REL] ([HIERARCHY_ID], [MASTER_LIST_ID], [TABLE_NAME]) VALUES (1, 2, N'C_PRD_PRODUCT_REL')
/****** Object:  Table [dbo].[Table_1]    Script Date: 09/16/2011 14:36:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[M_PRODUCTS](
	[PRODUCT_ID] [float] NULL,
	[PRODUCT_NAME] [varchar](255) NULL,
	[PRODUCT_BRAND] [varchar](255) NULL,
	[PRODUCT_SIZE] [varchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[M_PRODUCTS] ([PRODUCT_ID], [PRODUCT_NAME], [PRODUCT_BRAND], [PRODUCT_SIZE]) VALUES (1, N'TV', N'SONY', N'12*24')
INSERT [dbo].[M_PRODUCTS] ([PRODUCT_ID], [PRODUCT_NAME], [PRODUCT_BRAND], [PRODUCT_SIZE]) VALUES (2, N'VIDEO', N'LG', N'12*24')
/****** Object:  UserDefinedFunction [dbo].[GET_validParent]    Script Date: 09/16/2011 14:36:59 ******/
SET ANSI_NULLS ON


-- 19 september 

alter table X_MASTER_LIST_COLUMNS
add IS_PRIMARY bit

update X_MASTER_LIST_COLUMNS 
set is_required= 1 
where list_col_id=1 and LIST_ID =2

update X_MASTER_LIST_COLUMNS 
set is_required= 0
where list_col_id >1 and LIST_ID =2